import pandas as pd
import numpy as np

def main(data08_10, data11, data12, data13, data14_18, dataTract):
    
    df08 = pd.read_csv(data08_10)
    df11 = pd.read_csv(data11)
    df12 = pd.read_csv(data12)
    df13 = pd.read_csv(data13)
    df14 = pd.read_csv(data14_18)
    dfTract = pd.read_csv(dataTract)

    #get unique tracts
    uTracts = set(df08['TRACT10'].tolist() + df11['tract_fips'].tolist() + df12['tract_fips'].tolist() + df13['tract_fips'].tolist() + df14['TRACT10'].tolist())

    #create final results df
    finaldf = pd.DataFrame()
    
    #create df columns
    finaldf['TRACT10'] = uTracts
    finaldf['changetype'] = 0
    
    finaldf['prov08'] = 0
    finaldf['prov09'] = 0
    finaldf['prov10'] = 0
    finaldf['prov11'] = 0
    finaldf['prov12'] = 0
    finaldf['prov13'] = 0
    finaldf['prov14'] = 0
    finaldf['prov15'] = 0
    finaldf['prov16'] = 0
    finaldf['prov17'] = 0
    finaldf['prov18'] = 0
    
    finaldf['area'] = 0

    for index, row in finaldf.itterows():
        tt = row['TRACT10']
        
        #fill in data from results dfs
        #2008-2010
        tdf = df08[df08['TRACT10']==tt]

        finaldf.iloc[index, finaldf.columns.get_loc('changetype')] = tdf['changetype'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov08')] = tdf['prov08'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov09')] = tdf['prov09'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov10')] = tdf['prov10'][0]
        
        #2011
        tdf = df11[df11['tract_fips']==tt]

        finaldf.iloc[index, finaldf.columns.get_loc('prov11')] = tdf['total_prov'][0]

        #2012
        tdf = df12[df12['tract_fips']==tt]

        finaldf.iloc[index, finaldf.columns.get_loc('prov12')] = tdf['total_prov'][0]

        #2013
        tdf = df13[df13['tract_fips']==tt]

        finaldf.iloc[index, finaldf.columns.get_loc('prov13')] = tdf['total_prov'][0]
        
        #2014-2018
        tdf = df14[df14['TRACT10']==tt]
        
        finaldf.iloc[index, finaldf.columns.get_loc('prov14')] = tdf['prov14'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov15')] = tdf['prov15'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov16')] = tdf['prov16'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov17')] = tdf['prov17'][0]
        finaldf.iloc[index, finaldf.columns.get_loc('prov18')] = tdf['prov18'][0]
        
        #area
        tdf = dfTract[dfTract['tract_fips']==tt]
        
        finaldf.iloc[index, finaldf.columns.get_loc('area')] = tdf['area'][0]
        
    #now derive flag and delta vars
    finaldf['delta11_10'] = finaldf['prov11'] - finaldf['prov10']
    finaldf['delta18_08'] = finaldf['prov18'] - finaldf['prov08']
    finaldf['delta14_13'] = finaldf['prov14'] - finaldf['prov13']
    
    finaldf['flag3'] = np.where(abs(finaldf['delta11_10']) > 3, 1, 0)
    finaldf['flag4'] = np.where(abs(finaldf['delta11_10']) > 4, 1, 0)
    finaldf['flag5'] = np.where(abs(finaldf['delta11_10']) > 5, 1, 0)
    finaldf['flag13'] = np.where(abs(finaldf['prov13']) == 1, 1, 0)

    finaldf['TRACT10'] = finaldf['TRACT10'].apply(lambda x: x.zfill(11))
    
    #write the results
    finaldf.to_csv(r'path\results.csv')
    
if __name__ == '__main__':
    #change each of these paths to the correct location
    main(data08_10 = r'path\08_10results.csv', 
    data11 = r'path\hs_tractdata_v2_dec_2011.csv',
    data12 = r'path\hs_tractdata_v2_dec_2012.csv',
    data13 = r'path\hs_tractdata_v3_dec_2013.csv',
    data14_18 = r'path\14_18results.csv',
    dataTract = r'path\censusTract.csv')